﻿use persons
go
------------------7----------------------------------------------------------------
/*Find dads with amount of contacts more than 5*/
SELECT 
[Person].[ID],
[Person].[FirstName], 
[Person].[LastName] 
FROM [dbo].[Person], [dbo].[Contact]
WHERE [Person].[ID]=[Contact].[fk_personId] AND [Contact].[fk_personId] IN(SELECT DISTINCT 
[Person].[ID] 
FROM [dbo].[Person], [dbo].[Person] AS [FATHER] WHERE [Person].[ID]=[FATHER].[fk_fatherId])
GROUP BY [Person].[ID], [Person].[FirstName], [Person].[LastName] 
HAVING COUNT([Person].[ID])>5

SELECT 
[Person].[ID],
[Person].[FirstName], 
[Person].[LastName]
FROM [dbo].[Contact] INNER JOIN [dbo].[Person] 
ON [Contact].[fk_personId]=[Person].[ID] 
WHERE  [Contact].[fk_personId] IN(SELECT DISTINCT 
[Person].[ID] 
FROM [dbo].[Person] INNER JOIN [dbo].[Person] AS [FATHER]
ON [Person].[ID]=[FATHER].[fk_fatherId])
GROUP BY [Person].[ID], [Person].[FirstName], [Person].[LastName] 
HAVING COUNT([Person].[ID])>5

SELECT 
[Person].[ID], 
[Person].[FirstName], 
[Person].[LastName] 
FROM [dbo].[Person] INNER JOIN (SELECT 
[Contact].[fk_personId], 
COUNT([fk_personId]) [AMOUNT] 
FROM [dbo].[Contact] 
GROUP BY [fk_personId] 
HAVING COUNT([fk_personId])>5 ) [CONT]
ON ([Person].[ID] = [CONT].[fk_personId])
WHERE [ID] IN (SELECT DISTINCT  
[MOM].[ID] 
FROM [dbo].[Person] AS [MOM], [dbo].[Person] AS [DAD]
WHERE (([MOM].[ID] =[DAD].[fk_motherId]) or ([MOM].[ID] = [DAD].[fk_fatherId])))